create  table jms_dm.dm_terminal_deliver_punctuality_rate_detail_dt
(deliver_network_code   varchar(100) COMMENT '出仓网点code' ,
end_franchisee_code  varchar(100) COMMENT '末端加盟商code' ,
end_agent_code  varchar(100) COMMENT '末端代理区code' ,
billcode  varchar(100) COMMENT '运单' ,
end_agent_name   varchar(100) COMMENT '末端代理区name' ,
end_franchisee_name   varchar(100) COMMENT '末端加盟商' ,
send_next_station_code  varchar(100) COMMENT '下一站code' ,
send_next_station_name  varchar(100) COMMENT '下一站' ,
deliver_network_name   varchar(100) COMMENT '出仓网点' ,
end_provider_id   varchar(100) COMMENT '末端省id' ,
end_provider_name  varchar(100) COMMENT '末端省' ,
end_city_id   varchar(100) COMMENT '末端城市' ,
end_city_name  varchar(100) COMMENT '末端城市' ,
end_center_code   varchar(100) COMMENT '末中心code' ,
end_center_name   varchar(100) COMMENT '末中心' ,
end_center_send_time  varchar(100) COMMENT '末端中心发件时间' ,
planned_departure_time  varchar(100) COMMENT '规划接件时间' ,
planned_arrival_time    varchar(100) COMMENT '规划到件时间' ,
new_effective_plan_sign_time  varchar(100) COMMENT '规划派件时间' ,
span_days_shift               varchar(100) COMMENT '派件跨天' ,
deliver_shift                 varchar(100) COMMENT '派件班次' ,
end_network_arrival_time      varchar(100) COMMENT '末端网点到件时间' ,
deliver_time                  varchar(100) COMMENT 'h出仓时间' ,
deliver_type         int(6) comment '派件类型  1稍后出仓 2到出一体 3出仓 4其它'  ,
order_source_code             varchar(100) COMMENT '订单来源code' ,
order_source_name             varchar(100) COMMENT '订单来源' ,
deliver_township_id   varchar(100) COMMENT '出仓乡镇id' ,
deliver_township_name    varchar(100) COMMENT '出仓乡镇' ,
is_common varchar(100) COMMENT '是否共配' ,
new_effective_plan_deliver_date  date COMMENT '规划派件时间' ,
date_time   date COMMENT '发件日期' ,
end_network_code    varchar(100) COMMENT '末端到件网点code' ,
end_network_name    varchar(100) COMMENT '末端到件网点' ,
final_deliver_network_code   varchar(100) COMMENT '最终派件网点' ,
final_deliver_network_name   varchar(100) COMMENT '最终派件网点' ,
is_deliver_punctuality   varchar(100) COMMENT '是否派件准点' ,
delay_minutes decimal(16,2) COMMENT '延误分数' ,
plan_warehouse_end_time   varchar(100) COMMENT '新规划派件时间' ,
is_township  varchar(100) COMMENT '是否乡镇件' ,
special_extra_time decimal(16,2) comment '乡镇加时'
)
ENGINE=OLAP
DUPLICATE KEY(deliver_network_code,end_franchisee_code,end_agent_code)
comment '末端派件及时率明细'
PARTITION BY RANGE (date_time) (
   START ("2023-07-01") END ("2023-08-30") EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(deliver_network_code) BUCKETS 4
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-365",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "V2"
);
alter table jms_dm.dm_terminal_deliver_punctuality_rate_detail_dt add column (
        final_sign_user_code varchar(100) comment '派件员code'
       ,final_sign_user_name varchar(100) comment '派件员'
    ) ;